package game.adm.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import game.adm.dao.BonusDAO;
import game.adm.dao.dto.BonusDTO;
import game.adm.struts.form.BonusForm;
import game.web.base.Constants;
import game.web.base.DateUtils;
import projectx.core.base.ProjectXSystemException;
import projectx.core.dao.AbstractDAO;
import projectx.core.dao.DataAccessException;
import projectx.core.dao.ExceptionHandler;
import projectx.core.dao.impl.PreparedStatementBuilder;
import projectx.util.TicketUtil;

public class BonusDAOImpl extends AbstractDAO implements BonusDAO{

	public enum field {
		id, userid, gametype,gametypename,bettype,bettypename,bonus,maxbonus,returnpos,lockflag,createtime,updatetime,createip,updateip,username;
	}
	
	public BonusDAOImpl() throws ProjectXSystemException {
		super();
		// TODO Auto-generated constructor stub
	}

	@Override
	public boolean update(BonusForm bonusForm)
			throws DataAccessException {
		// TODO Auto-generated method stub
		int result = 0;
		PreparedStatement ps = null;
		String sql = null;
		double bonus = 10.0;
		double maxBonus = 50.0;
		double returnPos = 0.0;
		String ip = bonusForm.getIp();
		String bonusID = bonusForm.getBonusID();
		try{
			bonus = Double.parseDouble(bonusForm.getBonus());
		}catch(Exception e){
			logError(e);
		}
		try{
			maxBonus = Double.parseDouble(bonusForm.getMaxBonus());
		}catch(Exception e){
			logError(e);
		}
		try{
			returnPos = Double.parseDouble(bonusForm.getReturnPos());
		}catch(Exception e){
			logError(e);
		}
		
		try {
			_builder = (PreparedStatementBuilder) SqlLoader.getBuilders().get(
					"ADM_BonusDAO_UPDATE_BONUS");
			sql = _builder.getSql(null, null);
			ps = _builder.createStatement(_conn, sql, false, false);
			_builder.setParameter(ps, 1, bonus, Types.DOUBLE);
			_builder.setParameter(ps, 2, maxBonus, Types.DOUBLE);
			_builder.setParameter(ps, 3, returnPos, Types.DOUBLE);
			_builder.setParameter(ps, 4, DateUtils.getCurrentDate("yyyy-MM-dd HH:mm:ss"), Types.VARCHAR);
			_builder.setParameter(ps, 5, ip,Types.VARCHAR);
			_builder.setParameter(ps, 6, bonusID,Types.VARCHAR);
			
			debugSql();
			result = ps.executeUpdate();

		} catch (Exception e) {
			errorSql(e);
			_exHandler.handleUpdateException(e, ExceptionHandler.UNKNOWN);
		} finally {
			try {
				if (ps != null) {
					ps.close();
				}
			} catch (SQLException e) {
				logError(e);
			}
		}
		if (result == 0) {
			_exHandler.handleUpdateException(null, ExceptionHandler.NOTUPDATED);
		} else if (result > 1) {
			_exHandler.handleUpdateException(null,
					ExceptionHandler.MULTIUPDATED);
		}
		return true;
	}

	@Override
	public BonusDTO getBonusByID(String id) throws DataAccessException {
		// TODO Auto-generated method stub
		BonusDTO dto = null ;
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			_builder = (PreparedStatementBuilder) SqlLoader.getBuilders().get("ADM_BonusDAO_GET_BONUS_RECORD");
			String sql = _builder.getSql(null, null);
			ps = _builder.createStatement(_conn, sql, false, false);
			_builder.setParameter(ps, 1, id , Types.VARCHAR);
			debugSql();
			rs = ps.executeQuery();
			if(rs.next()){
				String bonusid = getString(rs.getString(field.id.ordinal()+1));
				String userid = getString(rs.getString(field.userid.ordinal()+1));
				String gametype = getString(rs.getString(field.gametype.ordinal()+1));
				String gametypename = getString(rs.getString(field.gametypename.ordinal()+1));
				String bettype = getString(rs.getString(field.bettype.ordinal()+1));
				String bettypename = getString(rs.getString(field.bettypename.ordinal()+1));
				double bonus = rs.getDouble(field.bonus.ordinal()+1);
				double maxbonus = rs.getDouble(field.maxbonus.ordinal()+1);
				double returnpos = rs.getDouble(field.returnpos.ordinal()+1);
				int lockflag = rs.getInt(field.lockflag.ordinal()+1);
				String createtime = rs.getString(field.createtime.ordinal()+1);
				String updatetime = rs.getString(field.updatetime.ordinal()+1);
				String createip = getString(rs.getString(field.createip.ordinal()+1));
				String updateip = getString(rs.getString(field.updateip.ordinal()+1));
				String username = getString(rs.getString(field.username.ordinal()+1));
				dto = new BonusDTO();
				dto.setId(bonusid);
				dto.setUserid(userid);
				dto.setGametype(gametype);
				dto.setGametypename(gametypename);
				dto.setBettype(bettype);
				dto.setBettypename(bettypename);
				dto.setBonus(bonus);
				dto.setMaxbonus(maxbonus);
				dto.setReturnpos(returnpos);
				dto.setLockflag(lockflag);
				dto.setCreatetime(createtime);
				dto.setUpdatetime(updatetime);
				dto.setCreateip(createip);
				dto.setUpdateip(updateip);
				dto.setUsername(username);
			}
		} catch (Exception e) {
			errorSql(e);
			_exHandler.handleQueryException(e, ExceptionHandler.UNKNOWN);
		} finally {
			try {
				if (null != rs) {
					rs.close();
				}
				if (null != ps) {
					ps.close();
				}
				if(null != _conn){
					_conn.close();
				}
			} catch (SQLException e) {
				logError(e);
			}
		}
		return dto;
	}
	
	private List<BonusDTO> toList(ResultSet rs) throws Exception{
		List<BonusDTO> list = new ArrayList<BonusDTO>();
		while (rs.next()) {
			String id = getString(rs.getString(field.id.ordinal()+1));
			String userid = getString(rs.getString(field.userid.ordinal()+1));
			String gametype = getString(rs.getString(field.gametype.ordinal()+1));
			String gametypename = getString(rs.getString(field.gametypename.ordinal()+1));
			String bettype = getString(rs.getString(field.bettype.ordinal()+1));
			String bettypename = getString(rs.getString(field.bettypename.ordinal()+1));
			double bonus = rs.getDouble(field.bonus.ordinal()+1);
			double maxbonus = rs.getDouble(field.maxbonus.ordinal()+1);
			double returnpos = rs.getDouble(field.returnpos.ordinal()+1);
			int lockflag = rs.getInt(field.lockflag.ordinal()+1);
			String createtime = rs.getString(field.createtime.ordinal()+1);
			String updatetime = rs.getString(field.updatetime.ordinal()+1);
			String createip = getString(rs.getString(field.createip.ordinal()+1));
			String updateip = getString(rs.getString(field.updateip.ordinal()+1));
			String username = getString(rs.getString(field.username.ordinal()+1));
			BonusDTO dto = new BonusDTO();
			dto.setId(id);
			dto.setUserid(userid);
			dto.setGametype(gametype);
			dto.setGametypename(gametypename);
			dto.setBettype(bettype);
			dto.setBettypename(bettypename);
			dto.setBonus(bonus);
			dto.setMaxbonus(maxbonus);
			dto.setReturnpos(returnpos);
			dto.setLockflag(lockflag);
			dto.setCreatetime(createtime);
			dto.setUpdatetime(updatetime);
			dto.setCreateip(createip);
			dto.setUpdateip(updateip);
			dto.setUsername(username);
			list.add(dto);
		}
		return list;
	}

	@Override
	public List<BonusDTO> listAdminBonus(BonusForm bonusForm)
			throws DataAccessException {
		// TODO Auto-generated method stub
		ResultSet rs = null;
		PreparedStatement ps = null;
		List<BonusDTO> list = null;
		StringBuilder sb = new StringBuilder();
		String gameType = bonusForm.getGameType();
		String betType = bonusForm.getBetType();
		int currentPage = 1;
		int pageSize = Constants.ADM_RECORD_PER_PAGE;
		try{
			currentPage = Integer.parseInt(bonusForm.getCurrentPage());
		}catch(Exception e){
			logError(e);
		}
		
		List<String> parameterValues = new ArrayList<String>();
		List<Integer> parameterTypes = new ArrayList<Integer>();
		if(null != gameType && gameType.length() > 0){
			sb.append(" and a.gametype =? ");
			parameterValues.add(gameType);
			parameterTypes.add(Types.VARCHAR);
		}
		if(null != betType && betType.length() > 0){
			sb.append(" and a.bettype =? ");
			parameterValues.add(betType);
			parameterTypes.add(Types.VARCHAR);
		}
		
		try {
			//总记录查询
			_builder = (PreparedStatementBuilder) SqlLoader.getBuilders().get("ADM_BonusDAO_SELECT_ADMIN_TOTAL_COUNT");
			String totalSQL = _builder.getSql(null, null) + sb.toString();
			ps = _builder.createStatement(_conn, totalSQL, false, false);
			fillParameters(_builder,ps,parameterValues,parameterTypes);
			debugSql();
			rs = ps.executeQuery();
			int totalRecord = 0;
			if(rs.next()){
				totalRecord = rs.getInt(1);
			}
			if(null != rs){
				rs.close();
			}
			if(null != ps){
				ps.close();
			}
			if(totalRecord <= 0){
				return list;
			}
			
			//分页查询记录
			_builder = (PreparedStatementBuilder) SqlLoader.getBuilders().get("ADM_BonusDAO_SELECT_ADMIN_BONUS");
			int totalPage = 0;
			if(totalRecord % pageSize == 0){
				totalPage = totalRecord / pageSize;
			}else{
				totalPage = totalRecord / pageSize + 1;
			}
			if(currentPage <= 0){
				currentPage = 1;
			}else if (currentPage > totalPage){
				currentPage = totalPage;
			}
			String hasPrevious = "false";
			String hasNext = "false";
			if(currentPage > 1){
				hasPrevious = "true";
			}
			if(currentPage < totalPage){
				hasNext = "true";
			}
			String limitSQL = _builder.getSql(null, null) + sb.toString() + " limit " + ((currentPage-1)*pageSize) + "," + pageSize;
			ps = _builder.createStatement(_conn, limitSQL, false, false);
			fillParameters(_builder,ps,parameterValues,parameterTypes);
			debugSql();
			rs = ps.executeQuery();
			list = toList(rs);
			
			bonusForm.setCurrentPage(new Integer(currentPage).toString());
			bonusForm.setFirstPage("1");
			bonusForm.setHasPrevious(hasPrevious);
			bonusForm.setHasNext(hasNext);
			bonusForm.setNextPage(new Integer(currentPage + 1).toString());
			bonusForm.setPreviousPage(new Integer(currentPage - 1).toString());
			bonusForm.setTotalPage(new Integer(totalPage).toString());
			bonusForm.setTotalRecord(new Integer(totalRecord).toString());
		} catch (Exception e) {
			errorSql(e);
			_exHandler.handleQueryException(e, ExceptionHandler.UNKNOWN);
		} finally {
			try {
				if (null != rs) {
					rs.close();
				}
				if (null != ps) {
					ps.close();
				}
				if(null != _conn){
					_conn.close();
				}
			} catch (SQLException e) {
				logError(e);
			}
		}
		return list;
	}

	@Override
	public List<BonusDTO> listUserBonus(BonusForm bonusForm)
			throws DataAccessException {
		// TODO Auto-generated method stub
		ResultSet rs = null;
		PreparedStatement ps = null;
		List<BonusDTO> list = null;
		StringBuilder sb = new StringBuilder();
		String userName = bonusForm.getUserName();
		String gameType = bonusForm.getGameType();
		String betType = bonusForm.getBetType();
		int currentPage = 1;
		int pageSize = Constants.ADM_RECORD_PER_PAGE;
		try{
			currentPage = Integer.parseInt(bonusForm.getCurrentPage());
		}catch(Exception e){
			logError(e);
		}
		
		List<String> parameterValues = new ArrayList<String>();
		List<Integer> parameterTypes = new ArrayList<Integer>();
		if(null != userName && userName.length() > 0){
			sb.append(" and b.username like ? ");
			parameterValues.add("%" + userName + "%");
			parameterTypes.add(Types.VARCHAR);
		}
		if(null != gameType && gameType.length() > 0){
			sb.append(" and a.gametype =? ");
			parameterValues.add(gameType);
			parameterTypes.add(Types.VARCHAR);
		}
		if(null != betType && betType.length() > 0){
			sb.append(" and a.bettype =? ");
			parameterValues.add(betType);
			parameterTypes.add(Types.VARCHAR);
		}
		
		try {
			//总记录查询
			_builder = (PreparedStatementBuilder) SqlLoader.getBuilders().get("ADM_BonusDAO_SELECT_USER_TOTAL_COUNT");
			String totalSQL = _builder.getSql(null, null) + sb.toString();
			ps = _builder.createStatement(_conn, totalSQL, false, false);
			fillParameters(_builder,ps,parameterValues,parameterTypes);
			debugSql();
			rs = ps.executeQuery();
			int totalRecord = 0;
			if(rs.next()){
				totalRecord = rs.getInt(1);
			}
			if(null != rs){
				rs.close();
			}
			if(null != ps){
				ps.close();
			}
			if(totalRecord <= 0){
				return list;
			}
			
			//分页查询记录
			_builder = (PreparedStatementBuilder) SqlLoader.getBuilders().get("ADM_BonusDAO_SELECT_USER_BONUS");
			int totalPage = 0;
			if(totalRecord % pageSize == 0){
				totalPage = totalRecord / pageSize;
			}else{
				totalPage = totalRecord / pageSize + 1;
			}
			if(currentPage <= 0){
				currentPage = 1;
			}else if (currentPage > totalPage){
				currentPage = totalPage;
			}
			String hasPrevious = "false";
			String hasNext = "false";
			if(currentPage > 1){
				hasPrevious = "true";
			}
			if(currentPage < totalPage){
				hasNext = "true";
			}
			String limitSQL = _builder.getSql(null, null) + sb.toString() + " limit " + ((currentPage-1)*pageSize) + "," + pageSize;
			ps = _builder.createStatement(_conn, limitSQL, false, false);
			fillParameters(_builder,ps,parameterValues,parameterTypes);
			debugSql();
			rs = ps.executeQuery();
			list = toList(rs);
			
			bonusForm.setCurrentPage(new Integer(currentPage).toString());
			bonusForm.setFirstPage("1");
			bonusForm.setHasPrevious(hasPrevious);
			bonusForm.setHasNext(hasNext);
			bonusForm.setNextPage(new Integer(currentPage + 1).toString());
			bonusForm.setPreviousPage(new Integer(currentPage - 1).toString());
			bonusForm.setTotalPage(new Integer(totalPage).toString());
			bonusForm.setTotalRecord(new Integer(totalRecord).toString());
		} catch (Exception e) {
			errorSql(e);
			_exHandler.handleQueryException(e, ExceptionHandler.UNKNOWN);
		} finally {
			try {
				if (null != rs) {
					rs.close();
				}
				if (null != ps) {
					ps.close();
				}
				if(null != _conn){
					_conn.close();
				}
			} catch (SQLException e) {
				logError(e);
			}
		}
		return list;
	}

	@Override
	public boolean initial(BonusForm bonusForm) throws DataAccessException {
		// TODO Auto-generated method stub
		ResultSet rs = null;
		PreparedStatement ps = null;
		double bonus = 10.0;
		double maxBonus = 50.0;
		double returnPos = 0.0;
		String ip = bonusForm.getIp();
		try{
			bonus = Double.parseDouble(bonusForm.getBonus());
		}catch(Exception e){
			logError(e);
		}
		try{
			maxBonus = Double.parseDouble(bonusForm.getMaxBonus());
		}catch(Exception e){
			logError(e);
		}
		try{
			returnPos = Double.parseDouble(bonusForm.getReturnPos());
		}catch(Exception e){
			logError(e);
		}
		
		try {
			//游戏类型查询(除快乐8外)
			List<String> gameIDList = new ArrayList<String>();
			_builder = (PreparedStatementBuilder) SqlLoader.getBuilders().get("ADM_GameTypeDAO_SELECT_OTHER_ID");
			String querySQL = _builder.getSql(null, null);
			ps = _builder.createStatement(_conn, querySQL, false, false);
			debugSql();
			rs = ps.executeQuery();
			while(rs.next()){
				gameIDList.add(getString(rs.getString(1)));
			}
			if(null != rs){
				rs.close();
			}
			if(null != ps){
				ps.close();
			}
			
			//游戏类型查询(快乐8)
			List<String> kenoIDList = new ArrayList<String>();
			_builder = (PreparedStatementBuilder) SqlLoader.getBuilders().get("ADM_GameTypeDAO_SELECT_KENO_ID");
			querySQL = _builder.getSql(null, null);
			ps = _builder.createStatement(_conn, querySQL, false, false);
			debugSql();
			rs = ps.executeQuery();
			while(rs.next()){
				kenoIDList.add(getString(rs.getString(1)));
			}
			if(null != rs){
				rs.close();
			}
			if(null != ps){
				ps.close();
			}
			
			//下注类型查询
			List<String> betIDList = new ArrayList<String>();
			_builder = (PreparedStatementBuilder) SqlLoader.getBuilders().get("ADM_BetTypeDAO_SELECT_ID");
			querySQL = _builder.getSql(null, null);
			ps = _builder.createStatement(_conn, querySQL, false, false);
			debugSql();
			rs = ps.executeQuery();
			while(rs.next()){
				betIDList.add(getString(rs.getString(1)));
			}
			if(null != rs){
				rs.close();
			}
			if(null != ps){
				ps.close();
			}
			
			List<String[]> playList = new ArrayList<String[]>();
			if(null != gameIDList && gameIDList.size() > 0){
				for(int i = 0;i < gameIDList.size(); i ++){
					if(null != betIDList && betIDList.size() > 0){
						for(int j = 0; j < betIDList.size() ; j ++){
							String[] str = new String[2];
							str[0] = getString(gameIDList.get(i));
							str[1] = getString(betIDList.get(j));
							playList.add(str);
						}
					}
				}
			}
			if(null != kenoIDList && kenoIDList.size() > 0){
				Iterator it = kenoIDList.iterator();
				while(it.hasNext()){
					String[] str = new String[2];
					str[0] = getString(it.next());
					str[1] = "-1";
					playList.add(str);
				}
			}
			
			if(null != playList && playList.size() > 0){
				//删除之前的模版数据
				_builder = (PreparedStatementBuilder) SqlLoader.getBuilders().get("ADM_BonusDAO_DELETE_ADMIN_BONUS");
				String deleteSQL = _builder.getSql(null, null);
				ps = _builder.createStatement(_conn, deleteSQL, false, false);
				debugSql();
				ps.executeUpdate();
				if(null != ps){
					ps.close();
				}
				
				_builder = (PreparedStatementBuilder) SqlLoader.getBuilders().get("ADM_BonusDAO_INSERT_BONUS");
				String insertSQL = _builder.getSql(null, null);
				String currentTime = DateUtils.getCurrentDate("yyyy-MM-dd hh:mm:ss");
				Iterator it = playList.iterator();
				while(it.hasNext()){
					String[] str = (String[]) it.next();
					ps = _builder.createStatement(_conn, insertSQL, false, false);
					_builder.setParameter(ps, 1, TicketUtil.getTicket(Constants.ADM_BONUS_INDEX) , Types.VARCHAR);
					_builder.setParameter(ps, 2, str[0] , Types.VARCHAR);
					_builder.setParameter(ps, 3, str[1] , Types.VARCHAR);
					_builder.setParameter(ps, 4, bonus , Types.DOUBLE);
					_builder.setParameter(ps, 5, maxBonus , Types.DOUBLE);
					_builder.setParameter(ps, 6, returnPos , Types.DOUBLE);
					_builder.setParameter(ps, 7, currentTime , Types.VARCHAR);
					_builder.setParameter(ps, 8, currentTime , Types.VARCHAR);
					_builder.setParameter(ps, 9, ip , Types.VARCHAR);
					_builder.setParameter(ps, 10, ip , Types.VARCHAR);
					debugSql();
					ps.executeUpdate();
					if(null != ps){
						ps.close();
					}
				}
				
				_conn.commit();
			}
		} catch (Exception e) {
			try {
				_conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			errorSql(e);
			_exHandler.handleQueryException(e, ExceptionHandler.UNKNOWN);
			return false;
		} finally {
			try {
				if (null != rs) {
					rs.close();
				}
				if (null != ps) {
					ps.close();
				}
				if(null != _conn){
					_conn.close();
				}
			} catch (SQLException e) {
				logError(e);
			}
		}
		return true;
	}

	private void fillParameters(PreparedStatementBuilder builder,PreparedStatement ps,List<String> parameterValues,List<Integer> parameterTypes) throws Exception{
		if(null != parameterValues && parameterValues.size() >0){
			for(int i = 0 ;i < parameterValues.size() ; i ++){
				int type = Integer.parseInt(parameterTypes.get(i).toString());
				builder.setParameter(ps, (i + 1), parameterValues.get(i) , type);
			}
		}
	}

	@Override
	public boolean isInitialized() throws DataAccessException {
		// TODO Auto-generated method stub
		return false;
	}
}
